package com.epam.web_project.bank.dao.account_dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.ResourceBundle;

import org.apache.log4j.Logger;

import com.epam.web_project.bank.dao.factory.MySQLDAOFactory;
import com.epam.web_project.bank.entity.Account;
import com.epam.web_project.bank.entity.Card;
import com.epam.web_project.bank.entity.Currency;
import com.epam.web_project.bank.exception.UnimplemetedException;
import com.epam.web_project.bank.util.AppPropertiesLoader;
import com.epam.web_project.bank.util.CurrencyStringConverter;

public class MySQLAccountDAO implements AccountDAO {
	private Logger logger = Logger.getLogger(MySQLAccountDAO.class);
	private Connection conn = null;
	PreparedStatement ps = null;
	ResultSet resultSet = null;
	AppPropertiesLoader props = AppPropertiesLoader.getInstance();
	private final String FIND_BY_CUSTOMER_ID_QUERY = "select a.id,a.iban,"
			+ "a.on_approval, a.credit_lim,a.blocked,c.number,c.type, a.amount,"
			+ "a.currency,c.expires from accounts a inner join cards c on "
			+ "c.id=a.card_id inner join users u on u.id=? inner join "
			+ "customer_account ca on ca.cust_id=u.id and ca.account_id=a.id"
			+ " where a.on_approval=? and a.deleted='0';";
	private final String FIND_BY_ID_QUERY = "select a.amount,a.credit_lim,"
			+ "a.blocked,a.iban, c.number,c.type,c.cvv, a.currency,c.expires "
			+ "from accounts a inner join cards c on a.card_id=c.id "
			+ "where a.id=?;";
	private final String FIND_BY_CARD_NAME = "select a.id, a.amount,a.currency,"
			+ "u.first_name,u.last_name from customer_account ca inner "
			+ "join users u	on ca.cust_id=u.id inner join accounts a on "
			+ "ca.account_id=a.id inner join cards car on car.id=a.card_id "
			+ "where car.number=? and u.first_name=? and u.last_name=?;";
	private final String BLOCK_ACCOUNT_QUERY = "update accounts set blocked='1'"
			+ " where id=?;";
	private final String INSERT_CARD_QUERY = "insert into cards(type) values(?);";
	private final String FIND_MAX_CARD_ID_QUERY = "select max(id) from cards;";
	private final String INSERT_ACCOUNT_QUERY = "insert into accounts(currency,"
			+ "credit_lim,card_id) values(?,?,?);";
	private final String INSERT_CUST_ACCOUNT_QUERY = "insert into "
			+ "customer_account (cust_id, account_id) values (?,?);";
	private final String FIND_ACCOUNT_ID_QUERY = "select max(id) from accounts;";
	private String UPDATE_ACCOUNT_QUERY = "update cards c, accounts a set "
			+ "c.number=?, c.expires=?,c.cvv=?, c.on_approval='0',a.iban=?,"
			+ "a.amount=?,a.credit_lim=?,a.blocked=?,a.on_approval='0' "
			+ "where a.id=? and a.card_id=c.id;";
	private final String UNBLOCK_QUERY = "update accounts set blocked='0' "
			+ "where id=?;";
	private final String MARK_ACCOUNT_DELETED_QUERY = "update accounts set deleted='1'"
			+ "where id=?;";
	private final String MARK_CARD_DELETED_QUERY = "update cards set deleted='1'"
			+ " where id=?;";
	private final String FIND_CARD_ID_BY_ACC_ID = "select a.card_id from accounts a"
			+ " where id=?;";

	public MySQLAccountDAO() {
	}

	@Override
	public boolean save(Account account) {
		conn = MySQLDAOFactory.takeConnection();
		try {
			if (conn != null && !conn.isClosed()) {
				conn.setAutoCommit(false); // start transaction
				PreparedStatement psInsertCard = conn
						.prepareStatement(INSERT_CARD_QUERY);
				psInsertCard.setString(1, account.getCard().getCardType());
				if (psInsertCard.executeUpdate() == 1) { // if the card inserted
					PreparedStatement psMaxCardId = conn
							.prepareStatement(FIND_MAX_CARD_ID_QUERY);
					ResultSet rsMaxCardId = psMaxCardId.executeQuery();
					int cardId;
					if (rsMaxCardId.next()) {
						cardId = rsMaxCardId.getInt(1);
					} else {
						cardId = 1;
					}
					ps = conn.prepareStatement(INSERT_ACCOUNT_QUERY);
					String currency = CurrencyStringConverter
							.convertToString((account.getCurrency()));
					ps.setString(1, currency);
					ps.setBigDecimal(2, account.getCreditLimit());
					ps.setInt(3, cardId);
					if (ps.executeUpdate() == 1) { // if the account inserted
						PreparedStatement psFindAccId = conn
								.prepareStatement(FIND_ACCOUNT_ID_QUERY);
						ResultSet rsFindAccId = psFindAccId.executeQuery();
						int maxAccId = 1;
						if (rsFindAccId.next()) {
							maxAccId = rsFindAccId.getInt(1);
						}
						PreparedStatement psInsertCustAcc = conn
								.prepareStatement(INSERT_CUST_ACCOUNT_QUERY);
						psInsertCustAcc.setInt(1, account.getOwnerId());
						psInsertCustAcc.setInt(2, maxAccId);
						if (psInsertCustAcc.executeUpdate() == 1) {
							conn.commit(); // Commit transaction
							return true;
						} else {
							conn.rollback();
							return false;
						}
					} else {
						conn.rollback();
						return false;
					}
				} else {
					conn.rollback();
					return false;
				}
			} else {
				return false;
			}
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return false;
	}

	@Override
	public boolean save(List<Account> accounts) throws UnimplemetedException {
		throw new UnimplemetedException();
	}

	@Override
	public boolean update(Account account) {
		conn = MySQLDAOFactory.takeConnection();
		try {
			if (conn != null && !conn.isClosed()) {
				conn.setAutoCommit(false);
				ps = conn.prepareStatement(UPDATE_ACCOUNT_QUERY);
				ps.setString(1, account.getCard().getNumber());
				ps.setDate(2, account.getCard().getExpires());
				ps.setString(3, account.getCard().getCvv());
				ps.setString(4, account.getIban());
				ps.setBigDecimal(5, account.getAmount());
				ps.setBigDecimal(6, account.getCreditLimit());
				ps.setBoolean(7, account.isBlocked());
				ps.setInt(8, account.getId());
				if (ps.executeUpdate() == 2) {
					conn.commit();
					return true;
				} else {
					conn.rollback();
					return false;
				}
			} else {
				return false;
			}
		} catch (SQLException e) {
			logger.error(e);
			return false;
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
	}

	@Override
	public boolean markDeleted(Account account) {
		conn = MySQLDAOFactory.takeConnection();
		try {
			if (conn != null && !conn.isClosed()) {
				int accountId = account.getId();
				conn.setAutoCommit(false);
				ps = conn.prepareStatement(MARK_ACCOUNT_DELETED_QUERY);
				ps.setInt(1, accountId);
				if (ps.executeUpdate() == 1) {
					PreparedStatement psFindCardId = conn
							.prepareStatement(FIND_CARD_ID_BY_ACC_ID);
					psFindCardId.setInt(1, accountId);
					ResultSet rsFindCardId = psFindCardId.executeQuery();
					int cardId = -1;
					if (rsFindCardId.next()) {
						cardId = rsFindCardId.getInt(1);
					}
					PreparedStatement psDeleteCard = conn
							.prepareStatement(MARK_CARD_DELETED_QUERY);
					psDeleteCard.setInt(1, cardId);
					if (psDeleteCard.executeUpdate() == 1) {
						conn.commit();
						return true;
					} else {
						conn.rollback();
						return false;
					}
				} else {
					conn.rollback();
					return false;
				}
			} else {
				return false;
			}
		} catch (SQLException e) {
			logger.error(e);
			return false;
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
	}

	@Override
	public boolean delete(Account account) throws UnimplemetedException {
		throw new UnimplemetedException();
	}

	@Override
	public Account findByCurrency(String currency) throws UnimplemetedException {
		throw new UnimplemetedException();
	}

	@Override
	public List<Account> findByCustomerId(int customerId, boolean onApproval) {
		Card card = null;
		List<Account> accounts = new ArrayList<Account>();
		conn = MySQLDAOFactory.takeConnection();
		try {
			if (conn != null && !conn.isClosed()) {
				ps = conn.prepareStatement(FIND_BY_CUSTOMER_ID_QUERY);
				ps.setInt(1, customerId);
				ps.setBoolean(2, onApproval);
				resultSet = ps.executeQuery();
				while (resultSet.next()) {
					Account account = new Account();
					card = new Card();
					card.setCardType(resultSet.getString("type"));
					card.setNumber(resultSet.getString("number"));
					card.setExpires(resultSet.getDate("expires"));
					account.setAmount(resultSet.getBigDecimal("amount"));
					account.setCard(card);
					account.setCurrency(CurrencyStringConverter
							.convertToCurrency(resultSet.getString("currency")));
					account.setId(resultSet.getInt("id"));
					account.setIban(resultSet.getString("iban"));
					account.setCreditLimit(resultSet
							.getBigDecimal("credit_lim"));
					account.setBlocked(resultSet.getBoolean("blocked"));
					account.setOnApproval(resultSet.getBoolean("on_approval"));
					accounts.add(account);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return accounts;
	}

	@Override
	public Account findById(int id) {
		Account account = null;
		Card card = null;
		ps = null;
		resultSet = null;
		conn = MySQLDAOFactory.takeConnection();
		try {
			if (conn != null && !conn.isClosed()) {
				ps = conn.prepareStatement(FIND_BY_ID_QUERY);
				ps.setInt(1, id);
				resultSet = ps.executeQuery();
				if (resultSet.next()) {
					account = new Account();
					account.setAmount(resultSet.getBigDecimal("amount"));
					account.setId(id);
					account.setIban(resultSet.getString("iban"));
					account.setCreditLimit(resultSet
							.getBigDecimal("credit_lim"));
					card = new Card();
					card.setCvv(resultSet.getString("cvv"));
					card.setExpires(resultSet.getDate("expires"));
					card.setCardType(resultSet.getString("type"));
					card.setNumber(resultSet.getString("number"));
					account.setCard(card);
					account.setBlocked(resultSet.getBoolean("blocked"));
					account.setCurrency(CurrencyStringConverter
							.convertToCurrency(resultSet.getString("currency")));
				}
			}
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return account;
	}

	@Override
	public Account findByCustomerNameAndCard(String firstName, String lastName,
			String cardNumber) {
		Account account = null;
		conn = MySQLDAOFactory.takeConnection();
		try {
			if (conn != null && !conn.isClosed()) {
				ps = conn.prepareStatement(FIND_BY_CARD_NAME);
				ps.setString(1, cardNumber);
				ps.setString(2, firstName);
				ps.setString(3, lastName);
				resultSet = ps.executeQuery();
				if (resultSet.next()) {
					account = new Account();
					BigDecimal amount = new BigDecimal(
							resultSet.getString("amount"));
					account.setAmount(amount);
					Currency currency = null;
					if (resultSet.getString("currency").equals("EUR")) {
						currency = Currency.EUR;
					}
					if (resultSet.getString("currency").equals("USD")) {
						currency = Currency.USD;
					}
					if (resultSet.getString("currency").equals("KZT")) {
						currency = Currency.KZT;
					}
					account.setCurrency(currency);
					account.setId(resultSet.getInt("id"));
					Card card = new Card();
					card.setNumber(cardNumber);
					account.setCard(card);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return account;
	}

	@Override
	public String block(Account account) {
		String res = "";
		int id = account.getId();
		conn = MySQLDAOFactory.takeConnection();
		try {
			if (conn != null && !conn.isClosed()) {
				conn.setAutoCommit(false);
				ps = conn.prepareStatement(BLOCK_ACCOUNT_QUERY);
				ps.setInt(1, id);
				if (ps.executeUpdate() == 1) {
					conn.commit();
					res = props.getProperty("BLOCK_ACCOUNT_SUCCESS");
					return res;
				} else {
					conn.rollback();
					res = props.getProperty("BLOCK_ACCOUNT_FAIL");
					return res;
				}
			}
		} catch (SQLException e) {
			logger.error(e);
			res = props.getProperty("BLOCK_ACCOUNT_FAIL");
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return res;
	}

	@Override
	public String unblock(Account account) {
		String res = "";
		int id = account.getId();
		conn = MySQLDAOFactory.takeConnection();
		try {
			if (conn != null && !conn.isClosed()) {
				conn.setAutoCommit(false);
				ps = conn.prepareStatement(UNBLOCK_QUERY);
				ps.setInt(1, id);
				if (ps.executeUpdate() == 1) {
					conn.commit();
					res = props.getProperty("UNBLOCK_ACCOUNT_SUCCESS");
					return res;
				} else {
					conn.rollback();
					res = props.getProperty("UNBLOCK_ACCOUNT_FAIL");
					return res;
				}
			}
		} catch (SQLException e) {
			logger.error(e);
			res = props.getProperty("UNBLOCK_ACCOUNT_FAIL");
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return res;
	}

}
